﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Curriculum.DataSet;
using E4RPTools;

namespace Curriculum
{
    public class Database
    {

        private static string GetCandidateVacanciesFilter(string extendedFilter)
        {
            if (extendedFilter.Length == 0)
                return "";
            string filter = extendedFilter.Replace("[Name]", "[CandidateName]");
            filter = filter.Replace("[BirthDate]", "convert(char(10), [BirthDate], 105)");
            filter = filter.Replace("[PreviousPositionStart]", "convert(char(10), [PreviousPositionStart], 105)");
            filter = filter.Replace("[PreviousPositionEnd]", "convert(char(10), [PreviousPositionEnd], 105)");
            filter = filter.Replace("[Start]", "convert(char(10), [Start], 105)");
            filter = filter.Replace("[AcceptingStart]", "convert(char(10), [AcceptingStart], 105)");
            filter = filter.Replace("[AcceptingEnd]", "convert(char(10), [AcceptingEnd], 105)");
            filter = filter.Replace("[ChangeDate]", "convert(char(10), [ChangeDate], 105)");
            return " and " + filter;
        }

        private static string GetCandidatesFilter(string extendedFilter)
        {
            if (extendedFilter.Length == 0)
                return "";
            string filter = extendedFilter.Replace("[Name]", "[CandidateName]");
            filter = filter.Replace("[BirthDate]", "convert(char(10), [BirthDate], 105)");
            filter = filter.Replace("[PreviousPositionStart]", "convert(char(10), [PreviousPositionStart], 105)");
            filter = filter.Replace("[PreviousPositionEnd]", "convert(char(10), [PreviousPositionEnd], 105)");            
            filter = filter.Replace("[ChangeDate]", "convert(char(10), [ChangeDate], 105)");
            return " and " + filter;
        }

        public static CandidateVacancyDataSet GetCandidateVacancies(SqlTransaction sqlTransaction, string extendedFilter)
        {
            var dataSet = new CandidateVacancyDataSet();
            const string query =
                @"  select	c.CandidateId, CandidateName as Name, BirthDate, Sex, Address, State, Province, City, ZipCode, Photo, AttachmentType,
	                        Email, Phone1, Phone2, EducationName, OccupationName, OccupationDescription, TaxNumber, Password, PhotoType, AttachmentName, CivilSituation,
	                        ComputerKnowledge, DriverLicenceType, PreviousPosition, PreviousPositionStart, PreviousPositionEnd, PreviousCompany, c.Status,
                            case  when Attachment is null then 0 else 1 end as HasAttachment,
                            v.VacancyId, Company, Area, Position, Description, Start, AcceptingStart, AcceptingEnd, Reference, Opennings, SubArea, c.ChangeDate, Password
                    from	Candidate c, CandidateVacancy cv, Vacancy v
                    where   cv.CandidateId = c.CandidateId
                            and v.VacancyId = cv.VacancyId
                            {0}
                    order by CandidateName";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.CandidateVacancy, string.Format(query, GetCandidateVacanciesFilter(extendedFilter)));
            return dataSet;
        }

        public static CandidateDataSet GetCandidates(SqlTransaction sqlTransaction, string extendedFilter)
        {
            var dataSet = new CandidateDataSet();
            const string query =
                @"  select	CandidateId as Id, CandidateName as Name, BirthDate, Sex, Address, State, Province, City, ZipCode, Photo, AttachmentType,
	                        Email, Phone1, Phone2, EducationName, OccupationName, OccupationDescription, TaxNumber, Password, PhotoType, AttachmentName, CivilSituation,
	                        ComputerKnowledge, DriverLicenceType, PreviousPosition, PreviousPositionStart, PreviousPositionEnd, PreviousCompany, Status,
                            case  when Attachment is null then 0 else 1 end as HasAttachment, ChangeDate
                    from	Candidate
                    where   1 = 1 {0}
                    order by CandidateName";
	        const string vacancyQuery =
                @"  select  cv.VacancyId, cv.CandidateId as Id, cv.Status
                    from    Candidate c, CandidateVacancy cv 
                    where   cv.CandidateId = c.CandidateId {0}";
            const string actionQuery =
                @"  select	ca.ActionId, ca.CandidateId as Id, ca.Status, ca.Reason, ca.Remarks, ca.Date, ca.Interviewer
                    from	Candidate c, CandidateAction ca
                    where	ca.CandidateId = c.CandidateId {0}
                    order by ca.ActionId";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Candidate, string.Format(query, GetCandidatesFilter(extendedFilter)));
                dataAccess.Fill(sqlTransaction, dataSet.CandidateVacancy, string.Format(vacancyQuery, GetCandidatesFilter(extendedFilter)));
                dataAccess.Fill(sqlTransaction, dataSet.CandidateAction, string.Format(actionQuery, GetCandidatesFilter(extendedFilter)));
            }
            return dataSet;
        }

        public static CandidateDataSet GetCandidate(SqlTransaction sqlTransaction, long candidateId)
        {
            var dataSet = new CandidateDataSet();
            const string query =
                @"  select	CandidateId as Id, CandidateName as Name, BirthDate, Sex, Address, State, Province, City, ZipCode, Photo, AttachmentType,
	                        Email, Phone1, Phone2, EducationName, OccupationName, OccupationDescription, TaxNumber, Password, PhotoType, AttachmentName, CivilSituation,
	                        ComputerKnowledge, DriverLicenceType, PreviousPosition, PreviousPositionStart, PreviousPositionEnd, PreviousCompany, Status,
                            case  when Attachment is null then 0 else 1 end as HasAttachment, ChangeDate
                    from	Candidate
                    where   CandidateId = @Id
                    order by CandidateName";
            const string vacancyQuery =
                @"  select  VacancyId, CandidateId as Id, Status
                    from    CandidateVacancy
                    where   CandidateId = @Id";
            const string actionQuery =
                @"  select	ActionId, CandidateId as Id, Status, Reason, Remarks, Date, Interviewer
                    from	CandidateAction
                    where   CandidateId = @Id
                    order by ActionId";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Candidate, query, new SqlParameter("Id", candidateId));
                dataAccess.Fill(sqlTransaction, dataSet.CandidateVacancy, vacancyQuery, new SqlParameter("Id", candidateId));
                dataAccess.Fill(sqlTransaction, dataSet.CandidateAction, actionQuery, new SqlParameter("Id", candidateId));
            }
            return dataSet;
        }

        public static byte[] GetCandidateAttachment(SqlTransaction sqlTransaction, long candidateId)
        {
            const string query =
                @"  select	Attachment
                    from	Candidate
                    where   CandidateId = @Id";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<byte[]>(sqlTransaction, null, query,new SqlParameter("Id", candidateId));
        }

        public static bool ExistsCandidateVacancies(SqlTransaction sqlTransaction, long vacancyId)
        {
            const string query =
                @"  select  count(*)
                    from    CandidateVacancy
                    where   VacancyId = @Id";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Id", vacancyId)) > 0;
        }

        public static CandidatePrintDataSet GetCandidatePrint(SqlTransaction sqlTransaction, long candidateId, long? vacancyId)
        {
            var dataSet = new CandidatePrintDataSet();
            const string query =
                @"  select	CandidateId as Id, CandidateName as Nome, BirthDate as DataNascimento, Sex as Sexo, Address as Morada, State as Distrito, 
                            Province as Concelho, City as Localidade, ZipCode as CodigoPostal, Photo as Fotografia, Email, Phone1 as Telefone1, 
                            Phone2 as Telefone2, EducationName as Habilitacoes, OccupationName as SituacaoProfissional, OccupationDescription as Outra, 
                            TaxNumber as Contribuinte, CivilSituation as EstadoCivil, ComputerKnowledge as ConhecimentosInformaticos, DriverLicenceType as CartaConducao, 
                            PreviousPosition as FuncaoAnterior, PreviousPositionStart as InicioAnterior, PreviousPositionEnd as FimAnterior, PreviousCompany as EmpresaAnterior, 
                            Status as Estado, ChangeDate as Alterado
                    from	Candidate
                    where   CandidateId = @Id";
            var vacancyQuery =
                @"  select  c.VacancyId, CandidateId as Id, Status as Estado,
                            v.Area, v.SubArea, v.Position as Funcao, v.Description as Descricao, v.Company as Empresa, v.Reference as Referencia
                    from    CandidateVacancy c, Vacancy v
                    where   CandidateId = @Id
                            and v.VacancyId = c.VacancyId";
            const string actionQuery =
                @"  select	ActionId, CandidateId as Id, Status as Estado, Reason as Motivo, Remarks as Observacoes, Date as Data, Interviewer as Entrevistador
                    from	CandidateAction
                    where   CandidateId = @Id";
            var sqlParameters = new List<SqlParameter> { new SqlParameter("Id", candidateId) };
            if(vacancyId.HasValue)
            {
                sqlParameters.Add(new SqlParameter("VacancyId", vacancyId.Value));
                vacancyQuery += " and v.VacancyId = @VacancyId";
            }
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Candidate, query, new SqlParameter("Id", candidateId));
                dataAccess.Fill(sqlTransaction, dataSet.CandidateVacancy, vacancyQuery, sqlParameters.ToArray());
                dataAccess.Fill(sqlTransaction, dataSet.CandidateAction, actionQuery, new SqlParameter("Id", candidateId));
            }
            return dataSet;
        }

        public static void InsertAction(SqlTransaction sqlTransaction, long candidateId, string status, string reason, string remarks, DateTime dateTime, string interviewer)
        {
            const string query =
                @"  insert 
                    into    CandidateAction(CandidateId, Status, Reason, Remarks, Date, Interviewer) 
                    values  (@CandidateId, @Status, @Reason, @Remarks, @Date, @Interviewer)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("CandidateId", candidateId),
                                        new SqlParameter("Status", status),
                                        new SqlParameter("Reason", reason),
                                        new SqlParameter("Remarks", remarks),
                                        new SqlParameter("Date", dateTime == DateTime.MinValue ? Convert.DBNull : dateTime),
                                        new SqlParameter("Interviewer", interviewer)                                        
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void UpdateAction(SqlTransaction sqlTransaction, long actionId, string reason, string remarks, DateTime dateTime, string interviewer)
        {
            const string query =
                @"  update  CandidateAction
                    set     Reason = @Reason, 
                            Remarks = @Remarks, 
                            Date = @Date, 
                            Interviewer = @Interviewer
                    where   ActionId = @ActionId";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("ActionId", actionId),
                                        new SqlParameter("Reason", reason),
                                        new SqlParameter("Remarks", remarks),
                                        new SqlParameter("Date", dateTime == DateTime.MinValue ? Convert.DBNull : dateTime),
                                        new SqlParameter("Interviewer", interviewer)                                        
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void UpdateCandidateStatus(SqlTransaction sqlTransaction, long candidateId, string status)
        {
            const string disableTrigger = "ALTER TABLE Candidate DISABLE TRIGGER CandidateChangeDate";
            const string enableTrigger = "ALTER TABLE Candidate ENABLE TRIGGER CandidateChangeDate";
            const string query =
                @"  update  Candidate
                    set     Status = @Status
                    where   CandidateId = @CandidateId";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("CandidateId", candidateId),
                                        new SqlParameter("Status", status)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, disableTrigger);
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, enableTrigger);
            }
        }

        #region Vacancy

        private static long GetVacancyId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(VacancyId)
                    from    Vacancy";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertVacancy(SqlTransaction sqlTransaction, VacancyDataSet.VacancyRow vacancy)
        {
            const string query =
                @"  insert 
                    into    Vacancy(Area, SubArea, Position, Description, Start, AcceptingStart, AcceptingEnd, Reference, Opennings) 
                    values  (@Area, @SubArea, @Position, @Description, @Start, @AcceptingStart, @AcceptingEnd, @Reference, @Opennings)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Area", vacancy.Area),
                                        new SqlParameter("SubArea", vacancy.SubArea),
                                        new SqlParameter("Position", vacancy.Position),
                                        new SqlParameter("Description", vacancy.IsDescriptionNull() ? Convert.DBNull : vacancy.Description),
                                        new SqlParameter("Start", (vacancy.IsStartNull() || vacancy.Start == DateTime.MinValue) ? Convert.DBNull : vacancy.Start),
                                        new SqlParameter("AcceptingStart", (vacancy.IsAcceptingStartNull() || vacancy.AcceptingStart == DateTime.MinValue) ? Convert.DBNull : vacancy.AcceptingStart),
                                        new SqlParameter("AcceptingEnd", (vacancy.IsAcceptingEndNull() || vacancy.AcceptingEnd == DateTime.MinValue) ? Convert.DBNull : vacancy.AcceptingEnd),
                                        new SqlParameter("Reference", vacancy.Reference),
                                        new SqlParameter("Opennings", vacancy.Opennings)                                        
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                vacancy.Id = GetVacancyId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateVacancy(SqlTransaction sqlTransaction, VacancyDataSet.VacancyRow vacancy)
        {
            const string query =
                @"  update  Vacancy
                    set     Area = @Area,
                            SubArea = @SubArea, 
                            Position = @Position, 
                            Description = @Description, 
                            Start = @Start, 
                            AcceptingStart = @AcceptingStart,
                            AcceptingEnd = @AcceptingEnd, 
                            Reference = @Reference, 
                            Opennings = @Opennings
                    where   VacancyId = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", vacancy.Id),
                                        new SqlParameter("Area", vacancy.Area),
                                        new SqlParameter("SubArea", vacancy.SubArea),
                                        new SqlParameter("Position", vacancy.Position),
                                        new SqlParameter("Description", vacancy.IsDescriptionNull() ? Convert.DBNull : vacancy.Description),
                                        new SqlParameter("Start", (vacancy.IsStartNull() || vacancy.Start == DateTime.MinValue) ? Convert.DBNull : vacancy.Start),
                                        new SqlParameter("AcceptingStart", (vacancy.IsAcceptingStartNull() || vacancy.AcceptingStart == DateTime.MinValue) ? Convert.DBNull : vacancy.AcceptingStart),
                                        new SqlParameter("AcceptingEnd", (vacancy.IsAcceptingEndNull() || vacancy.AcceptingEnd == DateTime.MinValue) ? Convert.DBNull : vacancy.AcceptingEnd),
                                        new SqlParameter("Reference", vacancy.Reference),
                                        new SqlParameter("Opennings", vacancy.Opennings)  
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static string GetVacanciesFilter(string extendedFilter)
        {
            if (extendedFilter.Length == 0)
                return "";
            string filter = extendedFilter.Replace("[Start]", "convert(char(10), [Start], 105)");
            filter = filter.Replace("[AcceptingStart]", "convert(char(10), [AcceptingStart], 105)");
            filter = filter.Replace("[AcceptingEnd]", "convert(char(10), [AcceptingEnd], 105)");            
            return " and " + filter;            
        }

        public static VacancyDataSet GetVacancies(SqlTransaction sqlTransaction, string extendedFilter)
        {
            var dataSet = new VacancyDataSet();
            const string query =
                @"  select	Company, Area, Position, Description, VacancyId as Id, Start, AcceptingStart, AcceptingEnd, Reference, Opennings, SubArea
                    from	Vacancy
                    where   1 = 1 {0}
                    order by Reference";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Vacancy, string.Format(query, GetVacanciesFilter(extendedFilter)));
            return dataSet;
        }

        public static VacancyDataSet GetVacancy(SqlTransaction sqlTransaction, long vacancyId)
        {
            var dataSet = new VacancyDataSet();
            const string query =
                @"  select	Company, Area, Position, Description, VacancyId as Id, Start, AcceptingStart, AcceptingEnd, Reference, Opennings, SubArea
                    from	Vacancy
                    where   VacancyId = @Id
                    order by Reference";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Vacancy, query, new SqlParameter("Id", vacancyId));
            return dataSet;
        }

        public static void SaveVacancy(SqlTransaction sqlTransaction, VacancyDataSet.VacancyRow vacancy)
        {
            if (vacancy.RowState == DataRowState.Added)
                InsertVacancy(sqlTransaction, vacancy);
            else
                UpdateVacancy(sqlTransaction, vacancy);
        }

        public static void DeleteVacancy(SqlTransaction sqlTransaction, long vacancyId)
        {
            const string query =
                @"  delete  Vacancy
                    where   VacancyId = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Id", vacancyId));
        }

        #endregion

        #region Position

        private static long GetPositionId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(PositionId)
                    from    Position";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertPosition(SqlTransaction sqlTransaction, PositionDataSet.PositionRow position)
        {
            const string query =
                @"  insert 
                    into    Position(Area, SubArea, PositionName) 
                    values  (@Area, @SubArea, @PositionName)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Area", position.Area),
                                        new SqlParameter("SubArea", position.SubArea),
                                        new SqlParameter("PositionName", position.Name)                                        
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                position.Id = GetPositionId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdatePosition(SqlTransaction sqlTransaction, PositionDataSet.PositionRow position)
        {
            const string query =
                @"  update  Position
                    set     Area = @Area,
                            SubArea = @SubArea, 
                            PositionName = @PositionName
                    where   PositionId = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", position.Id),
                                        new SqlParameter("Area", position.Area),
                                        new SqlParameter("SubArea", position.SubArea),
                                        new SqlParameter("PositionName", position.Name)    
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static string GetPositionsFilter(string extendedFilter)
        {
            if (extendedFilter.Length == 0)
                return "";
            string filter = extendedFilter.Replace("[Name]", "PositionName");
            return " and " + filter;             
        }

        public static PositionDataSet GetPositions(SqlTransaction sqlTransaction, string extendedFilter)
        {
            var dataSet = new PositionDataSet();
            const string query =
                @"  select	PositionId as Id, Area, SubArea, PositionName as Name
                    from	Position
                    where   1 = 1 {0}
                    order by Area, SubArea, PositionName";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Position, string.Format(query, GetPositionsFilter(extendedFilter)));
            return dataSet;
        }

        public static PositionDataSet GetPosition(SqlTransaction sqlTransaction, long positionId)
        {
            var dataSet = new PositionDataSet();
            const string query =
                @"  select	PositionId as Id, Area, SubArea, PositionName as Name
                    from	Position
                    where   PositionId = @Id
                    order by Area, SubArea, PositionName";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Position, query, new SqlParameter("Id", positionId));
            return dataSet;
        }

        public static void SavePosition(SqlTransaction sqlTransaction, PositionDataSet.PositionRow position)
        {
            if (position.RowState == DataRowState.Added)
                InsertPosition(sqlTransaction, position);
            else
                UpdatePosition(sqlTransaction, position);
        }

        #endregion


        public static OverviewDataSet GetCitiesTop(SqlTransaction sqlTransaction)
        {
            var dataSet = new OverviewDataSet();
            const string query =
                @"  select	count(v.Opennings) as total, 
                            State as name
                    from	Candidate c, CandidateVacancy cv, Vacancy v
                    where   cv.CandidateId = c.CandidateId
                            and v.VacancyId = cv.VacancyId
                            and State is not null
                    group by State";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Overview, query);
            return dataSet;
        }

        public static OverviewDataSet GetPositionsTop(SqlTransaction sqlTransaction)
        {
            var dataSet = new OverviewDataSet();
            const string query =
                @"  select	count(v.Opennings) as total, 
                            Position as name
                    from	Candidate c, CandidateVacancy cv, Vacancy v
                    where   cv.CandidateId = c.CandidateId
                            and v.VacancyId = cv.VacancyId
                            and Position is not null
                    group by Position";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Overview, query);
            return dataSet;
        }

        public static OverviewDataSet GetSexCount(SqlTransaction sqlTransaction)
        {
            var dataSet = new OverviewDataSet();
            const string query =
                @"  select	count(v.Opennings) as total, 
                            Sex as name
                    from	Candidate c, CandidateVacancy cv, Vacancy v
                    where   cv.CandidateId = c.CandidateId
                            and v.VacancyId = cv.VacancyId
                            and Sex is not null
                    group by Sex";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Overview, query);
            return dataSet;
        }

        public static OverviewDataSet GetEducationCount(SqlTransaction sqlTransaction)
        {
            var dataSet = new OverviewDataSet();
            const string query =
                @"  select	count(v.Opennings) as total, 
                            EducationName as name
                    from	Candidate c, CandidateVacancy cv, Vacancy v
                    where   cv.CandidateId = c.CandidateId
                            and v.VacancyId = cv.VacancyId
                            and EducationName is not null
                    group by EducationName";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Overview, query);
            return dataSet;
        }

        public static OverviewDataSet GetAgeCount(SqlTransaction sqlTransaction)
        {
            var dataSet = new OverviewDataSet();
            const string query =
                @"  select	count(v.Opennings) as total, 
                            DateDiff(yy, BirthDate, getdate()) as name
                    from	Candidate c, CandidateVacancy cv, Vacancy v
                    where   cv.CandidateId = c.CandidateId
                            and v.VacancyId = cv.VacancyId
                    group by DateDiff(yy, BirthDate, getdate())";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Overview, query);
            return dataSet;
        }

        #region Layout

        private static bool ExistsLayout(SqlTransaction sqlTransaction, string lista, string nome)
        {
            int count;
            const string query =
                @"  select	count(*)
		            from	ListaLayout
                    where   Lista = @Lista
                            and Nome = @Nome";
            using (var dataAccess = new SqlDataAccess())
                count = dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Lista", lista),
                                                 new SqlParameter("Nome", nome));
            return count > 0;
        }

        private static void InsertLayout(SqlTransaction sqlTransaction, string list, string name, string xmlLayout)
        {
            const string query =
                @"  insert 
                    into    ListaLayout(Lista, Nome, XmlLayout) 
                    values  (@List, @Name, @XmlLayout)";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("List", list),
                                              new SqlParameter("Name", name), new SqlParameter("XmlLayout", xmlLayout));
        }

        private static void UpdateLayout(SqlTransaction sqlTransaction, string lista, string nome, string xmlLayout)
        {
            const string query =
                @"  update  ListaLayout 
                    set     XmlLayout = @XmlLayout
                    where   Lista = @Lista
                            and Nome = @Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Lista", lista),
                                              new SqlParameter("Nome", nome), new SqlParameter("XmlLayout", xmlLayout));
        }


        public static ListaLayoutDataSet GetListLayouts(SqlTransaction sqlTransaction, string lista)
        {
            var dataSet = new ListaLayoutDataSet();
            const string query =
                @"  select	Lista, Nome, XmlLayout
		            from	ListaLayout
                    where   Lista = @Lista
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.ListaLayout, query, new SqlParameter("Lista", lista));
            return dataSet;
        }

        public static void SaveLayout(SqlTransaction sqlTransaction, string lista, string nome, string xmlLayout)
        {
            if (ExistsLayout(sqlTransaction, lista, nome))
                UpdateLayout(sqlTransaction, lista, nome, xmlLayout);
            else
                InsertLayout(sqlTransaction, lista, nome, xmlLayout);

        }

        #endregion
    }
}
